﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Koonsoft.HYCRM.BLL;
using org.in2bits.MyXls;

public partial class AnnualCheck_BuildCheckList : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void Button3_Click(object sender, EventArgs e)
    {
        var month = Convert.ToInt32(DropDownList2.SelectedValue);
        var branchID = Convert.ToInt32(DropDownList3.SelectedValue);
        int year = DateTime.Now.Year;
        var dt = new YearAuditBLL().GetAnnualCheckMissions(month, branchID, year);

        var month1 = DateTime.Parse(year + "-" + month + "-01").AddMonths(1).Month;
        var year1 = DateTime.Parse(year + "-" + month + "-01").AddMonths(1).Year;

        dt.Merge(new YearAuditBLL().GetAnnualCheckMissions(month1, branchID, year1));


        var month2 = DateTime.Parse(year + "-" + month + "-01").AddMonths(2).Month;
        var year2 = DateTime.Parse(year + "-" + month + "-01").AddMonths(2).Year;

        dt.Merge(new YearAuditBLL().GetAnnualCheckMissions(month2, branchID, year2));

        string branchName = new BranchBLL().GetBranchNameByBranchID(branchID);

        CreateExcel(dt, string.Format("{0}分行{1}月待检记录", branchName, month));
    }

    private void CreateExcel(DataTable dt, string xlsName)
    {
        XlsDocument xls = new XlsDocument();
        string fileName = DateTime.Now.ToString("yyyyMMddhhmmss");
        xls.FileName = fileName;

        int rowIndex = 1;
        int colIndex = 0;

        Worksheet sheet = xls.Workbook.Worksheets.Add(xlsName);//状态栏标题名称
        Cells cells = sheet.Cells;

        if ((dt != null) && (dt.Rows.Count > 0))
        {
            foreach (DataColumn col in dt.Columns)
            {
                colIndex++;
                cells.Add(1, colIndex, col.ColumnName);
            }

            foreach (DataRow row in dt.Rows)
            {
                rowIndex++;
                colIndex = 0;
                foreach (DataColumn col in dt.Columns)
                {
                    colIndex++;
                    Cell cell;
                    //sheet.Cells.AddValueCell(rowIndex, colIndex, row[col.ColumnName].ToString());//将数据添加到xls表格里
                    switch (row[col.ColumnName].ToString())
                    {
                        case "注册日期":
                            cell = cells.Add(rowIndex, colIndex, Convert.ToDateTime(row[col.ColumnName].ToString()).ToShortDateString());//转换为日期型
                            break;

                        case "参考成交价":
                            cell = cells.Add(rowIndex, colIndex, Convert.ToDateTime(row[col.ColumnName].ToString()));//转换为数字型
                            break;

                        default:
                            cell = cells.Add(rowIndex, colIndex, row[col.ColumnName].ToString());
                            break;
                    }

                    cell.Font.FontFamily = FontFamilies.Roman; //字体
                }
            }
        }

        //15874968285
        string filepath = HttpContext.Current.Server.MapPath("/Excel/");

        xls.Save(filepath);
        DownloadFile(filepath + fileName + ".xls", true);
    }

    /// <summary>
    /// 文件下载
    /// </summary>
    /// <param name="filename"></param>
    /// <param name="isDelete"></param>
    public static void DownloadFile(string filename, bool isDelete)
    {
        string name = Path.GetFileName(filename);
        var fileStream = new FileStream(filename, FileMode.Open);
        long fileSize = fileStream.Length;
        HttpContext.Current.Response.ContentType = "application/octet-stream";
        HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=\"" + HttpUtility.UrlEncode(name, System.Text.Encoding.UTF8) + "\";");

        HttpContext.Current.Response.AddHeader("Content-Length", fileSize.ToString());
        byte[] fileBuffer = new byte[fileSize];
        fileStream.Read(fileBuffer, 0, (int)fileSize);
        HttpContext.Current.Response.BinaryWrite(fileBuffer);
        fileStream.Close();
        if (isDelete)
        {
            File.Delete(filename);
        }
        HttpContext.Current.Response.End();

    }
}